﻿/********************************************************************************
    Copyright (C) Binod Nepal, Planet Earth Solutions Pvt. Ltd., Kathmandu.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MixNP.DatabaseLayer
{
    public static class Users
    {
        public static bool CreateUser(string emailAddress, string name, string nickName, string salt, string password)
        {
            string sql = "dbo.CreateUser";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@Name", name);
                command.Parameters.AddWithValue("@NickName", nickName);
                command.Parameters.AddWithValue("@Salt", salt);
                command.Parameters.AddWithValue("@Password", password);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static string GetNameFromEmailAddress(string emailAddress)
        {
            string sql = "SELECT Name FROM dbo.Users WHERE EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }

        public static string GetNickNameFromEmailAddress(string emailAddress)
        {
            string sql = "SELECT NickName FROM dbo.Users WHERE EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }

        public static string GetSaltFromEmailAddress(string emailAddress)
        {
            string sql = "SELECT Salt FROM dbo.Users WHERE EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }

        public static string GetProfilePictureExtension(string emailAddress)
        {
            string sql = "SELECT Extension FROM dbo.ProfilePictures WHERE EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }

        public static string GetEmailAddressFromSalt(string salt)
        {
            string sql = "SELECT EmailAddress FROM dbo.Users WHERE Salt=@Salt;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@Salt", salt);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }

        public static bool IsValidEmailAddress(string emailAddress)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Users WHERE EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static bool IsValidToken(string token)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Users WHERE Salt=@Salt;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@Salt", token);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static bool IsTokenConfirmed(string token)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Users WHERE Salt=@Salt AND IsConfirmed=1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@Salt", token);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static bool ConfirmRegistration(string token)
        {
            string sql = "UPDATE dbo.Users SET IsConfirmed=1, ConfirmedOn=GETUTCDATE() WHERE Salt=@Salt AND IsConfirmed=0;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@Salt", token);
                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static bool IsEmailConfirmed(string emailAddress)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Users WHERE EmailAddress=@EmailAddress AND IsConfirmed=1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static bool AuthenticatePassword(string emailAddress, string password, string salt)
        {
            string dbPassword = string.Empty;
            string hashedPassword = string.Empty;

            string sql = "SELECT Password FROM dbo.Users WHERE EmailAddress=@EmailAddress AND IsConfirmed=1 AND IsLockedOut=0 AND IsBanned=0;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                dbPassword = Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }

            hashedPassword = Pes.Utility.Conversion.HashSha512(dbPassword, salt);
            if (password.Equals(hashedPassword))
            {
                return true;
            }

            return false;
        }

        public static bool ChangePassword(string emailAddress, string password, string salt)
        {
            string sql = "UPDATE dbo.Users SET Password=@Password, Salt=@Salt WHERE EmailAddress=@EmailAddress AND IsConfirmed=1 AND IsLockedOut=0 AND IsBanned=0;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@Password", password);
                command.Parameters.AddWithValue("@Salt", salt);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static short GetCityId(string emailAddress)
        {
            string sql = "SELECT CityId FROM dbo.Profiles WHERE EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return Pes.Utility.Conversion.TryCastShort(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static string GetCity(string emailAddress)
        {
            string sql = "SELECT dbo.Cities.CityName FROM dbo.Profiles, dbo.Cities WHERE dbo.Profiles.CityId = dbo.Cities.CityId AND dbo.Profiles.EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static bool UpdateCity(string emailAddress, int cityId)
        {
            string sql = "UPDATE dbo.Profiles SET CityId=@CityId WHERE EmailAddress=@EmailAddress;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@CityId", cityId);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }        
        }

        /// <summary>
        /// Store the password reset request in database repository
        /// </summary>
        /// <param name="emailAddress">Email address of the account being reset.</param>
        /// <param name="recoveryMethod">Type of recovery method. Accepted values: 'E' and 'T'.</param>
        /// <param name="recoveryAddress">Email address or cell number for sending the recovery instructions</param>
        /// <returns>Generates a recovery token or returns the one stored in db.</returns>
        public static string PasswordResetQueue(string emailAddress, char recoveryMethod, string recoveryAddress, string token)
        {
            string sql = "IF NOT EXISTS(SELECT TOP 1 1 FROM dbo.PasswordResetRequests WHERE Status='Y' AND EmailAddress=@EmailAddress AND RecoveryMethod=@RecoveryMethod) BEGIN INSERT INTO dbo.PasswordResetRequests(EmailAddress, RecoveryMethod, RecoveryAddress, RecoveryToken) SELECT @EmailAddress, @RecoveryMethod, @RecoveryAddress, @RecoveryToken; SELECT @RecoveryToken; END ELSE BEGIN SELECT RecoveryToken FROM dbo.PasswordResetRequests WHERE Status='Y' AND EmailAddress=@EmailAddress AND RecoveryMethod=@RecoveryMethod; END";
            using (SqlCommand command = new SqlCommand(sql))
            { 
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@RecoveryMethod", recoveryMethod);
                command.Parameters.AddWithValue("@RecoveryAddress", recoveryAddress);
                command.Parameters.AddWithValue("@RecoveryToken", token);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static string GetEmailAddressFromRecoveryCode(string recoveryCode)
        {
            string sql = "SELECT EmailAddress FROM dbo.PasswordResetRequests WHERE RecoveryMethod='E' AND RecoveryToken=@RecoveryToken AND Status='Y';";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@RecoveryToken", recoveryCode);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }        
        }

        public static bool IsValidRecoveryCode(string recoveryCode, string emailAddress)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.PasswordResetRequests WHERE EmailAddress=@EmailAddress AND RecoveryMethod='T' AND RecoveryToken=@RecoveryToken AND Status='Y';";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@RecoveryToken", recoveryCode);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static bool IsValidRecoveryCode(string recoveryCode)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.PasswordResetRequests WHERE RecoveryMethod='E' AND RecoveryToken=@RecoveryToken AND Status='Y';";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@RecoveryToken", recoveryCode);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static bool FlagRecoveryCode(string recoveryCode, string emailAddress)
        {
            string sql = "UPDATE dbo.PasswordResetRequests SET RecoveredOn=GETUTCDATE() WHERE EmailAddress=@EmailAddress AND RecoveryMethod='T' AND RecoveryToken=@RecoveryToken AND Status='Y';";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@RecoveryToken", recoveryCode);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        public static bool FlagRecoveryCode(string recoveryCode)
        {
            string sql = "UPDATE dbo.PasswordResetRequests SET RecoveredOn=GETUTCDATE() WHERE RecoveryMethod='E' AND RecoveryToken=@RecoveryToken AND Status='Y';";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@RecoveryToken", recoveryCode);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }

        
    }
}
